﻿Imports System.Data.OleDb
Imports LecturerAppointmentSystem.UserDetails
Imports System.IO
Imports System.Net.Mail

Public Class AppointmentDetails
    Inherits System.Web.UI.Page

    Dim appointmentId As Integer
    Dim lecturerId As Integer
    Dim studentId As Integer
    Dim lecturerEmail As String
    Dim studentEmail As String
    Dim appointmentDate As String
    Dim appointmentTime As String
    Dim lecturerName As String
    Dim studentName As String
    Dim baseUrl As String
    Dim appointmentDetailsUrl As String
    Dim fullPath As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        appointmentId = CInt(Request.QueryString("ID"))
        manageButtonVisibility()
        If User.IsInRole("Student") Then
            AppointmentDetailsMultiView.SetActiveView(StudentView)
        Else
            AppointmentDetailsMultiView.SetActiveView(LecturerView)
        End If
        If IsPostBack Then
            LecturerDetailsView.DataBind()
            StudentDetailsView.DataBind()
            'lbl_BookingConfirmation.Visible = True
            'Page.DataBind()
        End If
    End Sub

    Protected Sub manageButtonVisibility()
        If isAppointmentBooked(appointmentId) Then
            btn_Cancel.Visible = True
            btn_Reschedule.Visible = True
            btn_Book.Visible = False
            BookPanel.Visible = False
            AgendaPanel.Visible = False
            CancelPanel.Visible = True
        Else
            btn_Cancel.Visible = False
            btn_Reschedule.Visible = False
            btn_Book.Visible = True
            BookPanel.Visible = True
            AgendaPanel.Visible = True
            CancelPanel.Visible = False
        End If
    End Sub

    Protected Sub BookAppointment(ByVal sender As Object, ByVal e As EventArgs)
        If Page.IsValid Then
            baseUrl = Request.Url.GetLeftPart(UriPartial.Authority) & Request.ApplicationPath
            appointmentDetailsUrl = "AppointmentDetails.aspx?ID=" & appointmentId
            fullPath = baseUrl & appointmentDetailsUrl
            lecturerId = getLecturerId()
            studentId = getUserId(User.Identity.Name)
            lecturerEmail = getLecturerEmail(lecturerId)
            studentEmail = getStudentEmail(studentId)
            lecturerName = getLecturerName(lecturerId)
            studentName = getStudentName(studentId)
            getAppointmentDetails(appointmentId)
            Dim replacements As ListDictionary = New ListDictionary()
            replacements.Add("<%LecturerName%>", lecturerName)
            replacements.Add("<%Date%>", appointmentDate)
            replacements.Add("<%Time%>", appointmentTime)
            replacements.Add("<%StudentName%>", studentName)
            replacements.Add("<%StudentEmail%>", studentEmail)
            replacements.Add("<%Link%>", fullPath)
            If Not isAppointmentBooked(appointmentId) Then
                markSlotAsBooked(appointmentId, studentId)
                sendMail(lecturerEmail, "Appointment Booked", "AppointmentBooked.txt", replacements)
                lbl_BookingConfirmation.Text = "Appointment booking successful"
                lbl_BookingConfirmation.Visible = True
            Else
                Response.Write("Sorry this slot is not available for booking. Please choose another slot.")
            End If
        Else
            lbl_BookingConfirmation.Text = "Please set an agenda for the appointment"
            lbl_BookingConfirmation.Visible = True
        End If
        lbl_BookingConfirmation.DataBind()
        manageButtonVisibility()
        btn_Book.DataBind()
        btn_Cancel.DataBind()
        btn_Reschedule.DataBind()
    End Sub

    'Protected Sub btn_Cancel_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_Cancel.Click
    '    CancelAppointmentPopup.Show()
    'End Sub

    Protected Sub ButtonSend_Click(ByVal sender As Object, ByVal e As EventArgs)
        baseUrl = Request.Url.GetLeftPart(UriPartial.Authority) & Request.ApplicationPath
        appointmentDetailsUrl = "AppointmentDetails.aspx?ID=" & appointmentId
        fullPath = baseUrl & appointmentDetailsUrl
        lecturerId = getLecturerId()
        studentId = getUserId(User.Identity.Name)
        lecturerEmail = getLecturerEmail(lecturerId)
        studentEmail = getStudentEmail(studentId)
        lecturerName = getLecturerName(lecturerId)
        studentName = getStudentName(studentId)
        getAppointmentDetails(appointmentId)
        Dim reason As String = tb_Reason.Text
        Dim replacements As ListDictionary = New ListDictionary()
        replacements.Add("<%Date%>", appointmentDate)
        replacements.Add("<%Time%>", appointmentTime)
        replacements.Add("<%Link%>", fullPath)
        replacements.Add("<%Reason%>", reason)
        cancelBooking(appointmentId)
        If User.IsInRole("Student") Then
            replacements.Add("<%ReceiverName%>", lecturerName)
            replacements.Add("<%SenderName%>", studentName)
            sendMail(lecturerEmail, "Request to cancel appointment", "AppointmentCancelled.txt", replacements)
        Else
            replacements.Add("<%ReceiverName%>", studentName)
            replacements.Add("<%SenderName%>", lecturerName)
            sendMail(studentEmail, "Appointment cancellation", "AppointmentCancelled.txt", replacements)
        End If
        lbl_BookingConfirmation.Text = "Appointment has been cancelled"
        lbl_BookingConfirmation.Visible = True
        lbl_BookingConfirmation.DataBind()
        manageButtonVisibility()
        btn_Book.DataBind()
        btn_Cancel.DataBind()
        btn_Reschedule.DataBind()
    End Sub

    Function isAppointmentBooked(ByVal appointment As Integer) As Boolean
        Dim booked As Boolean
        Dim sql As String = "SELECT Booked FROM Appointments WHERE ID = @ID"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@ID", appointment)
        conn.Open()
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        If reader.Read() Then
            booked = reader("Booked")
        End If
        conn.Close()
        Return booked
    End Function

    Protected Sub markSlotAsBooked(ByVal slot As Integer, ByVal bookedBy As Integer)
        Dim bookAppointmentSql As String = "UPDATE Appointments SET Booked = @Booked, Agenda = @Agenda WHERE ID = @ID"
        Dim bookAppointmentCmd As OleDbCommand = New OleDbCommand(bookAppointmentSql, conn)
        bookAppointmentCmd.Parameters.AddWithValue("@Booked", True)
        bookAppointmentCmd.Parameters.AddWithValue("@Agenda", tb_agenda.Text)
        bookAppointmentCmd.Parameters.AddWithValue("@ID", slot)
        Dim joinStudentAndAppointmentSql As String = "INSERT INTO BookedSlots(Fkid_AppointmentId, BookedBy) VALUES(@Fkid_AppointmentId, @BookedBy)"
        Dim joinStudentAndAppointmentCmd As OleDbCommand = New OleDbCommand(joinStudentAndAppointmentSql, conn)
        joinStudentAndAppointmentCmd.Parameters.AddWithValue("@Fkid_AppointmentId", slot)
        joinStudentAndAppointmentCmd.Parameters.AddWithValue("@BookedBy", bookedBy)
        conn.Open()
        bookAppointmentCmd.ExecuteNonQuery()
        joinStudentAndAppointmentCmd.ExecuteNonQuery()
        conn.Close()
        StudentDetailsView.DataBind()
    End Sub

    Protected Sub cancelBooking(ByVal slot As Integer)
        Dim cancelAppointmentSql As String = "UPDATE Appointments SET Booked = @Booked, Agenda = @Agenda WHERE ID = @ID"
        Dim cancelAppointmentCmd As OleDbCommand = New OleDbCommand(cancelAppointmentSql, conn)
        cancelAppointmentCmd.Parameters.AddWithValue("@Booked", False)
        cancelAppointmentCmd.Parameters.AddWithValue("@Agenda", "")
        cancelAppointmentCmd.Parameters.AddWithValue("@ID", slot)
        Dim cancelBookingSql As String = "DELETE * FROM BookedSlots WHERE Fkid_AppointmentId = @Fkid_AppointmentId"
        Dim cancelBookingCmd As OleDbCommand = New OleDbCommand(cancelBookingSql, conn)
        cancelBookingCmd.Parameters.AddWithValue("@Fkid_AppointmentId", slot)
        conn.Open()
        cancelAppointmentCmd.ExecuteNonQuery()
        cancelBookingCmd.ExecuteNonQuery()
        conn.Close()
        LecturerDetailsView.DataBind()
        StudentDetailsView.DataBind()
    End Sub

    Protected Sub rescheduleAppointment(ByVal oldSlot As Integer, ByVal newSlot As Integer, ByVal userRole As String)
        cancelBooking(oldSlot)
    End Sub

    Function getLecturerId() As Integer
        Dim lecturerUserId As Integer = -1
        Dim sql As String = "SELECT Lecturer FROM Appointments WHERE ID = @ID"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@ID", Request.QueryString("ID"))
        conn.Open()
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        If reader.Read() Then
            lecturerUserId = reader("Lecturer")
        End If
        conn.Close()
        Return lecturerUserId
    End Function

    Function getStudentId() As Integer
        Dim studentUserId As Integer = -1
        Dim sql As String = "SELECT BookedBy FROM BookedSlots WHERE Fkid_AppointmentId = @Fkid_AppointmentId"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@Fkid_AppointmentId", Request.QueryString("ID"))
        conn.Open()
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        If reader.Read() Then
            studentUserId = reader("BookedBy")
        End If
        conn.Close()
        Return studentUserId
    End Function

    Function getLecturerEmail(ByVal userId As Integer) As String
        Dim email As String = ""
        Dim sql As String = "SELECT UniversityEmail FROM Lecturer WHERE Fkid_UserId = @Fkid_UserId"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@Fkid_UserId", userId)
        conn.Open()
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        If reader.Read() Then
            email = reader("UniversityEmail")
        End If
        conn.Close()
        Return email
    End Function

    Function getStudentEmail(ByVal userId As Integer) As String
        Dim email As String = ""
        Dim sql As String = "SELECT UniversityEmail FROM Student WHERE Fkid_UserId = @Fkid_UserId"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@Fkid_UserId", userId)
        conn.Open()
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        If reader.Read() Then
            email = reader("UniversityEmail")
        End If
        conn.Close()
        Return email
    End Function

    Function getLecturerName(ByVal userId As Integer) As String
        Dim name As String = ""
        Dim sql As String = "SELECT FirstName, LastName FROM Lecturer WHERE Fkid_UserId = @Fkid_UserId"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@Fkid_UserId", userId)
        conn.Open()
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        If reader.Read() Then
            name = reader("FirstName") & " " & reader("LastName")
        End If
        conn.Close()
        Return name
    End Function

    Function getStudentName(ByVal userId As Integer) As String
        Dim name As String = ""
        Dim sql As String = "SELECT FirstName, LastName FROM Student WHERE Fkid_UserId = @Fkid_UserId"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@Fkid_UserId", userId)
        conn.Open()
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        If reader.Read() Then
            name = reader("FirstName") & " " & reader("LastName")
        End If
        conn.Close()
        Return name
    End Function

    Sub getAppointmentDetails(ByVal appointmentId As Integer)
        Dim sql As String = "SELECT * FROM Test2AppointmentsAndLecturerDetailsQuery WHERE ID = @ID"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@ID", appointmentId)
        conn.Open()
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        If reader.Read() Then
            appointmentDate = reader("AppointmentDate")
            appointmentTime = reader("TimeSlot")
        End If
        conn.Close()
    End Sub

    Protected Sub sendMail(ByVal reciever As String, ByVal subject As String, ByVal body As String, ByVal replacements As ListDictionary)
        Dim appointmentId As Integer = Request.QueryString("ID")
        Dim appPath As String = Request.PhysicalApplicationPath
        Dim reader As StreamReader = New StreamReader(appPath & "EmailTemplates\" & body)
        Dim mailMessage As MailMessage = New MailMessage()
        mailMessage.IsBodyHtml = True
        mailMessage.From = New MailAddress("ari.dk.roy@gmail.com")
        mailMessage.To.Add(New MailAddress(reciever))
        mailMessage.CC.Add(New MailAddress("ari.dk.roy@gmail.com"))
        mailMessage.Subject = subject
        mailMessage.Body = reader.ReadToEnd()
        reader.Close()
        Dim item As DictionaryEntry
        For Each item In replacements
            mailMessage.Body = mailMessage.Body.Replace(item.Key, item.Value)
        Next item
        Dim client As SmtpClient = New SmtpClient()
        client.EnableSsl = True
        Try
            client.Send(mailMessage)
        Catch ex As Exception
            'Dim userId As Integer = getUserId(tb_username.Text)
            'deleteUser(userId)
            lbl_MailError.Text = ex.ToString()
            lbl_MailError.Visible = True
        End Try
    End Sub

    Protected Sub btnRescheduleYes_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnRescheduleYes.Click
        Dim lecturerId As Integer = getLecturerId()
        lecturerName = Replace(getLecturerName(lecturerId), " ", "+")
        Response.Redirect("BookAppointment.aspx?name=" & lecturerName & "&ref=reschedule")
    End Sub
End Class